package server;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class GestorDB {

	private static GestorDB instance = null;
	private static Connection connection = null;
	private static Connection connectionAct = null;

	private GestorDB() throws ClassNotFoundException, SQLException {
		Class.forName("org.sqlite.JDBC");
		connection = java.sql.DriverManager
				.getConnection("jdbc:sqlite:db/drones.db");
		connectionAct = java.sql.DriverManager
				.getConnection("jdbc:sqlite:db/actualizaciones.db");
	}

	public static GestorDB getInstance() throws SQLException {
		if (instance == null) {
			try {
				instance = new GestorDB();
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
		}
		return instance;
	}

	public boolean validarUsuario(String username, String passw)
			throws SQLException {
		String query = "SELECT USERNAME, PASSWORD FROM LOGIN WHERE USERNAME = '"
				+ username + "' AND PASSWORD = '" + passw + "'";
		Statement stm = connection.createStatement();
		ResultSet rs1 = stm.executeQuery(query);
		if (!rs1.isClosed()) {
			rs1.close();
			stm.close();
			return true;
		} else {
			stm.close();
			return false;
		}
	}

	public int activarSensor(String id_sensor) throws SQLException {

		String query = "UPDATE SENSOR SET Status = 'true' WHERE ID_Sensor = '"
				+ id_sensor + "'";
		Statement stm = connection.createStatement();
		int control = stm.executeUpdate(query);
		stm.close();
		return control;
	}

	public int desactivarSensor(String id_sensor) throws SQLException {
		String query = "UPDATE SENSOR SET Status = 'false' WHERE ID_Sensor = '"
				+ id_sensor + "'";
		Statement stm = connection.createStatement();
		int control = stm.executeUpdate(query);
		stm.close();
		return control;
	}

	public String obtenerEstadoSensor(String id_sensor, String id_dron)
			throws SQLException {
		String estado = "";
		String query = "SELECT Status FROM SENSOR WHERE ID_SENSOR = '"
				+ id_sensor + "' AND ID_DRON = '" + id_dron + "'";
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery(query);
		while (rs.next()) {
			if (!rs.isClosed()) {
				estado = rs.getString("Status");
			}
		}
		stm.close();
		return estado;
	}

	public int activarGPS(String id_dron) throws SQLException {
		String query = "UPDATE DRON SET GPSStatus = 'true' WHERE ID_Dron = '"
				+ id_dron + "'";
		Statement stm = connection.createStatement();
		int control = stm.executeUpdate(query);
		stm.close();
		return control;
	}

	public int desactivarGPS(String id_dron) throws SQLException {
		String query = "UPDATE DRON SET GPSStatus = 'false' WHERE ID_Dron = '"
				+ id_dron + "'";
		Statement stm = connection.createStatement();
		int control = stm.executeUpdate(query);
		stm.close();
		return control;
	}

	public String obtenerEstadoGPS(String id_dron) throws SQLException {
		String estado = "";
		String query = "SELECT GPSStatus FROM DRON WHERE ID_Dron = '" + id_dron
				+ "'";
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery(query);
		while (rs.next()) {
			if (!rs.isClosed()) {
				estado = rs.getString("GPSStatus");
			}
		}
		stm.close();
		return estado;
	}

	public ArrayList<String> getListaSensores() throws SQLException {
		ArrayList<String> lSensores = new ArrayList<String>();
		String query = "SELECT * FROM SENSOR WHERE ID_Dron='"
				+ TCPServer.id_dron + "';";
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery(query);
		while (rs.next()) {
			String sensorData = rs.getString("ID_Sensor") + " "
					+ rs.getString("Description") + " "
					+ rs.getString("Status");
			lSensores.add(sensorData);
		}
		rs.close();
		stm.close();
		return lSensores;
	}

	public int obtenerValorActual(String id_sensor, String id_dron)
			throws SQLException {
		if (id_sensor == null)
			return 0;
		int valor;
		String query = "SELECT * FROM SENSOR WHERE ID_Sensor='" + id_sensor
				+ "' AND ID_Dron='" + id_dron + "'";
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery(query);

		valor = (int) (Math.random() * 200 - 100);

		stm.close();
		rs.close();
		return valor;
	}

	public boolean exeUpdate(String sentence) {
		try {
			Statement stm = connection.createStatement();
			stm.execute(sentence);
			return true;
		} catch (SQLException se) {
			System.out.println("Imposible ejecutar la sentencia!" + se);
			return false;
		}
	}

	public String obtenerEstadoDron(String id_dron) throws SQLException {
		String valor;
		String query = "SELECT DronStatus FROM DRON WHERE ID_Dron = '"
				+ id_dron + "'";
		Statement stm = connection.createStatement();
		ResultSet rs = stm.executeQuery(query);
		valor = rs.getString("DronStatus");
		return valor;
	}

	public int insertarUsuario(String username, String password)
			throws SQLException {

		String query = "INSERT INTO LOGIN VALUES ('" + username + "', '"
				+ password + "')";
		Statement stm = connection.createStatement();
		int control = stm.executeUpdate(query);
		stm.close();
		return control;
	}

	public int actualizarUsuario(String username, String password)
			throws SQLException {
		String query = "UPDATE LOGIN SET " + " password='" + password
				+ "' WHERE username= '" + username + "'";
		Statement stm = connection.createStatement();
		stm.close();
		int control = stm.executeUpdate(query);
		return control;
	}

	public int eliminarUsuario(String username) throws SQLException {
		String query = "DELETE FROM LOGIN WHERE username = '" + username + "'";
		Statement stm = connection.createStatement();
		int control = stm.executeUpdate(query);
		stm.close();
		return control;
	}

	public ArrayList<String> obtenerHistorico(String id_sensor, String id_dron)
			throws SQLException {
		if (id_sensor != null) {

			ArrayList<String> historico = new ArrayList<String>();
			String query = "SELECT * FROM HISTORICO, SENSOR WHERE HISTORICO.ID_Sensor=SENSOR.ID_Sensor AND HISTORICO.ID_Sensor='"
					+ id_sensor + "' AND SENSOR.ID_Dron='" + id_dron + "';";
			Statement stm = connection.createStatement();
			ResultSet rs = stm.executeQuery(query);
			while (rs.next()) {
				String medidas = "*" + rs.getString("DATE") + " "
						+ rs.getString("TIME") + " " + rs.getString("LATITUDE")
						+ " " + rs.getString("LONGITUDE") + " "
						+ rs.getString("VALUE");
				historico.add(medidas);
			}
			rs.close();
			stm.close();
			return historico;
		}
		return null;
	}

	public int getTamanyoHistorico() {
		String query = "SELECT * FROM HISTORICO;";
		Statement stm;
		int i = 0;
		try {
			stm = connection.createStatement();

			ResultSet rs = stm.executeQuery(query);
			if (rs.isClosed()) {
				return 0;
			}
			while (rs.next()) {
				i++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return i;
	}

	public static void releaseInstance() throws SQLException {
		connection.close();
		instance = null;
	}

	public String getpassAct(String id_dron) {
		String resultado = null;
		try {
			String query = "SELECT password FROM LOGIN WHERE username = '"
					+ id_dron + "'";
			Statement stm = connectionAct.createStatement();
			ResultSet rs = stm.executeQuery(query);
			resultado = rs.getString("password");
			stm.close();
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return resultado;
	}

	public boolean validarUsuarioAct(String username, String passw)
			throws SQLException {
		String query = "SELECT username, password FROM LOGIN WHERE username = '"
				+ username + "' AND password = '" + passw + "'";
		Statement stm = connectionAct.createStatement();
		ResultSet rs2 = stm.executeQuery(query);
		if (!rs2.isClosed()) {
			rs2.close();
			stm.close();
			return true;
		} else {
			stm.close();
			return false;
		}
	}
}